﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Collections;
using System.Data;

namespace LibFriesland_Campina_CRM
{
    public class DalInvoiceDetail
    {
        //Lay mot InvoiceDetail
        private static BizInvoiceDetail getInvoiceDetail(long pBy_invoice, string pBy_product)
        {
            Friesland_Campina_CRM_DataContext db = new Friesland_Campina_CRM_DataContext(new MySqlConnection(Thuvien.getConnString()));
            try
            {
                var invoiceDetail = db.InvoiceDetail.Single(p => p.ByInvoice == pBy_invoice && p.ByProduct == pBy_product);
                return new BizInvoiceDetail(invoiceDetail.ByInvoice, invoiceDetail.ByProduct, invoiceDetail.Quantity);
            }
            catch(Exception ex)
            {
                return null;
            }
        }
        
        //Them mot InvoiceDetail cho mot Invoice
        public static int addInvoiceDetail(BizInvoiceDetail pInvoiceDetail)
        {
            Friesland_Campina_CRM_DataContext db = new Friesland_Campina_CRM_DataContext(new MySqlConnection(Thuvien.getConnString()));
            try
            {
                InvoiceDetail invoiceDetail = new InvoiceDetail();
                invoiceDetail.ByInvoice = pInvoiceDetail.by_invoice;
                invoiceDetail.ByProduct = pInvoiceDetail.by_product;
                invoiceDetail.Quantity = pInvoiceDetail.quantity;
                invoiceDetail.Price = pInvoiceDetail.price;
                invoiceDetail.SaleComment = pInvoiceDetail.sale_comment;
                db.InvoiceDetail.InsertOnSubmit(invoiceDetail);
                db.SubmitChanges();
                return 1;
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }

        //Xoa InvoiceDetail cua mot Invoice
        public static int deleteInvoiceDetail(long pBy_invoice, string pBy_product)
        {
            Friesland_Campina_CRM_DataContext db = new Friesland_Campina_CRM_DataContext(new MySqlConnection(Thuvien.getConnString()));
            try
            {
                var invoiceDetail = db.InvoiceDetail.Single(p => p.ByInvoice == pBy_invoice && p.ByProduct == pBy_product);
                db.InvoiceDetail.DeleteOnSubmit(invoiceDetail);
                db.SubmitChanges();
                return 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //Cap nhat InvoiceDetail
        public static int updateInvoiceDetail(BizInvoiceDetail pInvoiceDetail)
        {
            Friesland_Campina_CRM_DataContext db = new Friesland_Campina_CRM_DataContext(new MySqlConnection(Thuvien.getConnString()));
            try
            {
                var invoiceDetail = db.InvoiceDetail.Single(p => p.ByInvoice == pInvoiceDetail.by_invoice && p.ByProduct == pInvoiceDetail.by_product);
                invoiceDetail.Quantity = pInvoiceDetail.quantity;
                invoiceDetail.SaleComment = pInvoiceDetail.sale_comment;
                db.SubmitChanges();
                return 1;
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }

        //Thay doi so luong san pham cua mot InvoiceDetail
        public static int changeInvoiceDetailQuantity(BizInvoiceDetail pInvoiceDetailChanging)
        {
            Friesland_Campina_CRM_DataContext db = new Friesland_Campina_CRM_DataContext(new MySqlConnection(Thuvien.getConnString()));
            try
            {
                //BizInvoiceDetail invoiceDetail = getInvoiceDetail(pInvoiceDetailChanging.by_invoice, pInvoiceDetailChanging.by_product);
                //Kiem tra so luong thay doi hop le
                if (pInvoiceDetailChanging.quantity > 0) //Neu so luong con lai khac 0
                {
                    return updateInvoiceDetail(pInvoiceDetailChanging);
                }
                else
                {
                    int numOfInvoiceDetail = BizInvoice.getInvoiceDetailbyInvoice(pInvoiceDetailChanging.by_invoice).Count; //Lay so luong InvoiceDetail cua mot Invoice
                    if (numOfInvoiceDetail > 1)
                        return deleteInvoiceDetail(pInvoiceDetailChanging.by_invoice, pInvoiceDetailChanging.by_product);
                    else //Neu chi con mot InvoiceDetail -> Xoa Invoice
                        return BizInvoice.deleteInvoice(pInvoiceDetailChanging.by_invoice);
                }
                
            }
            catch(Exception ex)
            {
                return -1;
            }
        }

        #region Dung cho Webservice
        //Lay thong tin chi tiet ban hang theo ngay cua NA tai Shop
        public static List<BizInvoiceDetail> getInvoiceDetailbyDateNAShop(DateTime pDatetime, string pBy_na, string pBy_shop)
        {
            Friesland_Campina_CRM_DataContext db = new Friesland_Campina_CRM_DataContext(new MySqlConnection(Thuvien.getConnString()));
            try
            {
                var invoiceDetails = from invoiceDetail in db.InvoiceDetail
                                     where invoiceDetail.Invoice.ByNA == pBy_na && invoiceDetail.Invoice.ByShop == pBy_shop
                                     select new BizInvoiceDetail(invoiceDetail.Product.Name, invoiceDetail.ByProduct, invoiceDetail.Quantity, invoiceDetail.Product.ByProductCatalogue, invoiceDetail.Product.ProductCatALouGe.Name, Thuvien.myConvertDatetimeToString(invoiceDetail.Invoice.DateTimeInvoice));

                List<BizInvoiceDetail> invoiceDetailsList = invoiceDetails.ToList();
                invoiceDetailsList = invoiceDetailsList.FindAll(
                                        delegate(BizInvoiceDetail item)
                                        {
                                            if (Convert.ToDateTime(item.datetime_invoice).Date == pDatetime.Date)
                                                return true;
                                            else
                                                return false;
                                        });
                return invoiceDetailsList;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        //Thong ke tong so luong ban duoc tung san pham cua NA theo ngay tai Shop
        public static List<BizProductSale> getSalebyDateNAShop(DateTime pDatetime, string pBy_na, string pBy_shop)
        {
            Friesland_Campina_CRM_DataContext db = new Friesland_Campina_CRM_DataContext(new MySqlConnection(Thuvien.getConnString()));
            try
            {
                List<BizProductSale> result = new List<BizProductSale>();
                List<BizInvoiceDetail> invoiceDetailList = getInvoiceDetailbyDateNAShop(pDatetime, pBy_na, pBy_shop);
                IEnumerable<IGrouping<string, int>> query = invoiceDetailList.GroupBy(p => p.by_product, p => p.quantity);
                foreach (IGrouping<string, int> itemGroup in query)
                {
                    string by_product = itemGroup.Key;
                    int total = 0;
                    foreach (int quantity in itemGroup)
                        total += quantity;
                    result.Add(new BizProductSale(by_product, total));
                }
                return result;
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        #endregion

        #region Dung cho Report
            //Thong ke so luong ban duoc tung san pham cua NA theo khoang thoi gian
                                                                                                                                                                                                                                                                                                                                                                                            public static Dataset.DatasetNaSale getSalebyDateNa(DateTime pFromDate, DateTime pToDate, string pBy_na)
        {
            Friesland_Campina_CRM_DataContext db = new Friesland_Campina_CRM_DataContext(new MySqlConnection(Thuvien.getConnString()));
            Dataset.DatasetNaSale ds = new Dataset.DatasetNaSale();
            DalBase dal = new DalBase();

            #region Tao du lieu cho bang SHOP
            string commandText = " select DISTINCT(shop_charid), name from na_shop "
                                + " inner join shop on shop_charid = by_shop "
                                + " where by_na = '" + pBy_na + "'";
            DataTable shopTable = dal.getData(commandText);
            foreach (DataRow item in shopTable.Rows)
            {
                ds.Tables["SHOP"].ImportRow(item);
            }
            #endregion

            #region Tao du lieu cho bang PRODUCT_CATALOGUE
            commandText = "select product_catalogue_charid, short_name as name, by_product_brand from product_catalouge";
            DataTable productCatalogue = dal.getData(commandText);
            foreach (DataRow item in productCatalogue.Rows)
            {
                ds.Tables["PRODUCT_CATALOGUE"].ImportRow(item);
            }
            #endregion

            #region Tao du lieu cho bang PRODUCT_BRAND
            commandText = "select product_brand_charid, name from product_brand";
            DataTable productBrand = dal.getData(commandText);
            foreach (DataRow item in productBrand.Rows)
            {
                ds.Tables["PRODUCT_BRAND"].ImportRow(item);
            }
            #endregion

            #region Tao du lieu cho bang PRODUCT
            commandText = " SELECT product_charid ,product.short_name as name, "
                        + " by_product_catalogue "
                        + " FROM product ";
            DataTable productTable = dal.getData(commandText);
            foreach (DataRow item in productTable.Rows)
            {
                ds.Tables["PRODUCT"].ImportRow(item);
            }
            #endregion

            #region Tao du lieu cho bang PRODUCT_BY_NA
            string fromDateStr = pFromDate.ToString("yyyy-MM-dd");
            string toDateStr = pToDate.ToString("yyyy-MM-dd");
            commandText = " SELECT product_in_shop.by_product, "
                        + " CASE "
                        + " WHEN (temp.quantity > 0) THEN temp.quantity else 0 "
                        + " END AS quantity, product_in_shop.by_shop, "
                        + " CASE "
                        + " WHEN "
                        + " temp.by_shopper is NULL THEN 1 else temp.by_shopper "
                        + " END as by_shopper "
                        + " FROM product_in_shop "
                        + " LEFT JOIN ( "
                        + " SELECT by_product , SUM(quantity) as quantity, by_shop, by_shopper "
                        + " FROM invoice_detail "
                        + " INNER JOIN invoice ON invoice_detail.by_invoice = invoice.invoice_numid "
                        + " WHERE by_na = '" + pBy_na + "'"
                        + " and DATE(datetime_phone_invoice) >='" + fromDateStr + "'"
                        + " and DATE(datetime_phone_invoice) <='" + toDateStr + "'"
                        + " GROUP BY by_product, by_shopper "
                        + " )temp ON product_in_shop.by_shop = temp.by_shop "
                        + " AND product_in_shop.by_product = temp.by_product ";
            DataTable productByNATable = dal.getData(commandText);
            foreach (DataRow item in productByNATable.Rows)
            {
                ds.Tables["PRODUCT_BY_NA"].ImportRow(item);
            }
            #endregion

            #region Tao du lieu cho bang SHOPPER
            //commandText = " select shopper_numid, nickname, name, phone from shopper "
            //            + " where shopper_numid = 1 or by_na ='" + pBy_na + "'"
            //            + " and datetime_create >='" + fromDateStr + "'"
            //            + " and datetime_create <='" + toDateStr + "'";
            commandText = " select shopper_numid, nickname, name, phone from shopper "
                       + " where shopper_numid = 1 or by_na ='" + pBy_na + "'";
            DataTable shopperTable = dal.getData(commandText);
            //if (shopperTable.Rows.Count == 1)
            //{
            //    DataRow row = ds.Tables["SHOPPER"].NewRow();
            //    row["shopper_numid"] = -1;
            //    row["nickname"] = "";
            //    row["name"] = "";
            //    row["phone"] = "";
            //    ds.Tables["SHOPPER"].Rows.Add(row);
            //}

            foreach (DataRow item in shopperTable.Rows)
            {
                ds.Tables["SHOPPER"].ImportRow(item);
            }
            #endregion

            return ds;
        }

            //Thong ke so luong ban duoc tung san pham cua Sup (tong cac san pham cua NA) theo khoang thoi gian
                                                                                                                                                                                                                                                                                                        public static Dataset.DatasetSupSale getSalebyDateSup(DateTime pFromDate, DateTime pToDate, string pBy_sup)
        {
            Friesland_Campina_CRM_DataContext db = new Friesland_Campina_CRM_DataContext(new MySqlConnection(Thuvien.getConnString()));
            Dataset.DatasetSupSale ds = new Dataset.DatasetSupSale();
            DalBase dal = new DalBase();

            #region Tao du lieu cho bang NA
            string commandText = "Select na_charid, name from na where by_sup ='" + pBy_sup + "'";
            DataTable naTable = dal.getData(commandText);
            foreach (DataRow item in naTable.Rows)
            {
                ds.Tables["NA"].ImportRow(item);
            }
            #endregion

            #region Tao du lieu cho bang PRODUCT_CATALOGUE
            commandText = "select product_catalogue_charid, short_name as name, by_product_brand from product_catalouge";
            DataTable productCatalogue = dal.getData(commandText);
            foreach (DataRow item in productCatalogue.Rows)
            {
                ds.Tables["PRODUCT_CATALOGUE"].ImportRow(item);
            }
            #endregion

            #region Tao du lieu cho bang PRODUCT_BRAND
            commandText = "select product_brand_charid, name from product_brand";
            DataTable productBrand = dal.getData(commandText);
            foreach (DataRow item in productBrand.Rows)
            {
                ds.Tables["PRODUCT_BRAND"].ImportRow(item);
            }
            #endregion

            #region Tao du lieu cho bang PRODUCT
            commandText = " SELECT product_charid ,product.short_name as name, "
                        + " by_product_catalogue "
                        + " FROM product ";
            DataTable productTable = dal.getData(commandText);
            foreach (DataRow item in productTable.Rows)
            {
                ds.Tables["PRODUCT"].ImportRow(item);
            }
            #endregion

            #region Tao du lieu cho bang PRODUCT_BY_NA
            string fromDateStr = pFromDate.ToString("yyyy-MM-dd");
            string toDateStr = pToDate.ToString("yyyy-MM-dd");
            commandText = " select "
                + " case when by_product is NULL then (select product_charid from product limit 0,1) else by_product end as by_product, "
                + " case when quantity is NULL then 0 else quantity end as quantity, "
                + " na_charid as by_na from na "
                + " left join "
                + " (select product_charid as by_product, "
                + " case when temp.quantity is NULL then 0 else temp.quantity end as quantity, "
                + " case when by_na is NULL then (select na_charid from na where by_sup ='" + pBy_sup + "' limit 0,1) else by_na "
                + " end as by_na from product"
                + " left join "
                + " (select by_product, sum(quantity) as quantity, by_na from invoice_detail "
                + " inner join invoice on invoice_detail.by_invoice = invoice.invoice_numid "
                + " inner join na on na_charid = by_na "
                + " where DATE(datetime_phone_invoice) >= '" + fromDateStr + "'"
                + " and DATE(datetime_phone_invoice) <= '" + toDateStr + "'"
                + " and by_na <> 'NADEFAULT' and by_na "
                + " <> 'natest' "
                + " and by_sup ='" + pBy_sup + "'"
                + " group by by_product, by_na) temp "
                + " on product_charid = by_product) temp "
                + " on na_charid = temp.by_na "
                + " where na_charid <> 'natest' and na_charid <> 'NADEFAULT' ";

            DataTable productByNaTable = dal.getData(commandText);
            foreach (DataRow item in productByNaTable.Rows)
            {
                ds.Tables["PRODUCT_BY_NA"].ImportRow(item);
            }
            #endregion

            return ds;
        }

            //Thong ke so luong ban duoc tung san pham cua shop
                                                                                                                                                                                                                                                            public static Dataset.DatasetShopSale getSalebyDateShop(DateTime pFromDate, DateTime pToDate)
        {
            Friesland_Campina_CRM_DataContext db = new Friesland_Campina_CRM_DataContext(new MySqlConnection(Thuvien.getConnString()));
            Dataset.DatasetShopSale ds = new Dataset.DatasetShopSale();
            DalBase dal = new DalBase();

            #region Tao du lieu cho bang Shop
            string commandText = "select shop_charid, name as shop_name from shop where shop_charid <> 'SHDEFAULT'";
            DataTable shopTable = dal.getData(commandText);
            foreach (DataRow item in shopTable.Rows)
            {
                ds.Tables["SHOP"].ImportRow(item);
            }
            #endregion

            #region Tao du lieu cho bang PRODUCT_CATALOGUE
            commandText = "select product_catalogue_charid, short_name as product_catalogue_name, by_product_brand from product_catalouge";
            DataTable productCatalogue = dal.getData(commandText);
            foreach (DataRow item in productCatalogue.Rows)
            {
                ds.Tables["PRODUCT_CATALOGUE"].ImportRow(item);
            }
            #endregion

            #region Tao du lieu cho bang PRODUCT_BRAND
            commandText = "select product_brand_charid, name as product_brand_name from product_brand";
            DataTable productBrand = dal.getData(commandText);
            foreach (DataRow item in productBrand.Rows)
            {
                ds.Tables["PRODUCT_BRAND"].ImportRow(item);
            }
            #endregion

            #region Tao du lieu cho bang PRODUCT
            commandText = " SELECT product_charid ,product.short_name as product_name, "
                        + " by_product_catalogue "
                        + " FROM product ";
            DataTable productTable = dal.getData(commandText);
            foreach (DataRow item in productTable.Rows)
            {
                ds.Tables["PRODUCT"].ImportRow(item);
            }
            #endregion

            #region Tao du lieu cho bang PRODUCT_BY_SHOP
            string fromDateStr = pFromDate.ToString("yyyy-MM-dd");
            string toDateStr = pToDate.ToString("yyyy-MM-dd");
            commandText = " select product_in_shop.by_product, "
                + " case when temp.quantity is null then 0 else temp.quantity end as quantity, "
                + " product_in_shop.by_shop "
                + " from product_in_shop "
                + " left join "
                + " (SELECT by_product, sum(quantity) as quantity, by_shop FROM invoice_detail "
                + " inner join invoice on by_invoice = invoice_numid "
                + " where by_na <> 'natest' and DATE(datetime_phone_invoice) >= '" + fromDateStr + "'"
                + " and DATE(datetime_phone_invoice) <= '" + toDateStr + "'"
                + " group by by_product, by_shop) temp on temp.by_product = "
                + " product_in_shop.by_product and temp.by_shop = product_in_shop.by_shop ";

            DataTable productByShopTable = dal.getData(commandText);
            foreach (DataRow item in productByShopTable.Rows)
            {
                ds.Tables["PRODUCT_BY_SHOP"].ImportRow(item);
            }
            #endregion

            return ds;
        }
        #endregion
    }

}
